package com.css.fxfzypg.modules.rating.task.dao;

import com.css.fxfzypg.modules.rating.task.model.entity.KpRatingTaskIndex3Score;
import com.css.fxfzypg.modules.rating.task.model.entity.KpRatingTaskIndex3ScoreExpert;
import com.css.fxfzypg.modules.rating.task.model.vo.KpRatingTaskIndex3ScoreVo;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

@Repository
public class KpRatingTaskIndex3ScoreRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public BigDecimal countSystemScore(KpRatingTaskIndex3Score kpRatingTaskIndex3Score) {
        StringBuilder sql = new StringBuilder("select count(system_score) from kp_rating_task_index3_score where 1=1 ");
        buildWhereSql(kpRatingTaskIndex3Score, sql);
        return jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(BigDecimal.class));
    }

    private static void buildWhereSql(KpRatingTaskIndex3Score kpRatingTaskIndex3Score, StringBuilder sql) {
        if (ObjectUtils.isNotEmpty(kpRatingTaskIndex3Score.getId())) {
            sql.append(" and id = '").append(kpRatingTaskIndex3Score.getId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskIndex3Score.getKesdi3Id())) {
            sql.append(" and kesdi3_id = '").append(kpRatingTaskIndex3Score.getKesdi3Id()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskIndex3Score.getSystemScore())) {
            sql.append(" and system_score = '").append(kpRatingTaskIndex3Score.getSystemScore()).append("' ");
        }
    }


    public BigDecimal countExpertScore(KpRatingTaskIndex3Score kpRatingTaskIndex3Score) {
        StringBuilder sql = new StringBuilder("select count(krti3se.score) from kp_rating_task_index3_score krti3s left join kp_rating_task_index3_score_expert krti3se on krti3s.id = krti3se.krti3s_id where 1=1");
        if (ObjectUtils.isNotEmpty(kpRatingTaskIndex3Score.getId())) {
            sql.append(" and krti3s.id = '").append(kpRatingTaskIndex3Score.getId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskIndex3Score.getKesdi3Id())) {
            sql.append(" and krti3s.kesdi3_id = '").append(kpRatingTaskIndex3Score.getKesdi3Id()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskIndex3Score.getSystemScore())) {
            sql.append(" and krti3s.system_score = '").append(kpRatingTaskIndex3Score.getSystemScore()).append("' ");
        }
        return jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(BigDecimal.class));
    }

    /**
     * @param kesdi3Id 3级指标关系表id
     * @param krtytId  关联任务id
     * @return
     */
    public KpRatingTaskIndex3ScoreVo getIndex3Score(String kesdi3Id, String krtytId) {
        String sql = "select *, (select sum(score) from kp_rating_task_index3_score_expert where krti3s_id = kp_rating_task_index3_score.id group by krti3s_id) expert_score from kp_rating_task_index3_score where kesdi3_id = '" + kesdi3Id + "'  and krtyt_id = '" + krtytId + "'";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(KpRatingTaskIndex3ScoreVo.class));
    }

    public BigDecimal getSystemScoreByKpesdi2IdAndKrtaId(String kpesdi2Id, String krtaId) {
        return jdbcTemplate.queryForObject(String.format("SELECT sum(system_score) from kp_rating_task_index3_score t1 LEFT JOIN kp_exam_scoring_db_index3 t2 on t1.kesdi3_id = t2.id where t1.krta_id ='%s' and t2.kpesdi2_id = '%s'", krtaId, kpesdi2Id), BigDecimal.class);
    }

    public BigDecimal getExpertScoreByKpesdi2IdAndKrtaId(String kpesdi2Id, String krtaId) {
        String sql = String.format("select sum(kp_rating_task_index3_score_expert.score) from kp_rating_task_index3_score_expert LEFT JOIN kp_exam_scoring_db_index3 on  kp_rating_task_index3_score_expert.kesdi3_id = kp_exam_scoring_db_index3.id LEFT JOIN kp_rating_task_area t3 on t3.krtyt_id = kp_rating_task_index3_score_expert.krtyt_id where t3.id = '%s' and kp_exam_scoring_db_index3.kpesdi2_id = '%s'", krtaId, kpesdi2Id);
        return jdbcTemplate.queryForObject(sql, BigDecimal.class);
    }

    /**
     * 查询某专家给当前关联任务打的三级指标的分数
     *
     * @param kesdi3Id 3级指标关系表id
     * @param krtytId  关联任务id
     * @param expertId 专家id
     * @return
     */
    public KpRatingTaskIndex3ScoreVo getGlTaskIndex3ScoreByExpertId(String kesdi3Id, String krtytId, String expertId) {
        String sql = "select *, (select sum(score) from kp_rating_task_index3_score_expert where krti3s_id = kp_rating_task_index3_score.id group by krti3s_id) expert_score from kp_rating_task_index3_score where kesdi3_id = '" + kesdi3Id + "'  and krtyt_id = '" + krtytId + "' and kp_rating_task_index3_score_expert.expert_id = '" + expertId + "'";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(KpRatingTaskIndex3ScoreVo.class));
    }

    public KpRatingTaskIndex3Score getByKrtytIdAndkesdi3Id(String krtytId, String kesdi3Id) {
        try {
            String sql = "select t1.id,t1.kesdi3_id,t1.system_score,t1.krta_id from kp_rating_task_index3_score t1 left join  kp_rating_task_area t2 on t1.krta_id = t2.id where t2.krtyt_id = '"+krtytId+"' and t1.kesdi3_id = '"+kesdi3Id+"' ";
            List<KpRatingTaskIndex3Score> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(KpRatingTaskIndex3Score.class));
            if (PlatformObjectUtils.isNotEmpty(list)){
                return list.get(0);
            }else {
                return null;
            }
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public void batchSave(List<KpRatingTaskIndex3Score> list) {
        String sql = "INSERT INTO kp_rating_task_index3_score(id, kesdi3_id, system_score, krta_id) values(:id, :kesdi3Id, :systemScore, :krtaId)";
        namedParameterJdbcTemplate.batchUpdate(sql, SqlParameterSourceUtils.createBatch(list.toArray()));
    }

    public void insert(KpRatingTaskIndex3Score kpRatingTaskIndex3Score) {
        namedParameterJdbcTemplate.update("INSERT INTO kp_rating_task_index3_score(id, kesdi3_id, system_score, krta_id) values(:id, :kesdi3Id, :systemScore, :krtaId)", new BeanPropertySqlParameterSource(kpRatingTaskIndex3Score));
    }

    public void updateById(KpRatingTaskIndex3Score index3Score) {
        StringBuilder sql = new StringBuilder("update kp_rating_task_index3_score set ");
        if (ObjectUtils.isNotEmpty(index3Score.getId())) {
            sql.append("  id = '").append(index3Score.getId()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(index3Score.getSystemScore())) {
            sql.append("  system_score = '").append(index3Score.getSystemScore()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(index3Score.getKesdi3Id())) {
            sql.append("  kesdi3_id = '").append(index3Score.getKesdi3Id()).append("', ");
        }
        if (ObjectUtils.isNotEmpty(index3Score.getKrtaId())) {
            sql.append("  krta_id = '").append(index3Score.getKrtaId()).append("', ");
        }
        sql.deleteCharAt(sql.lastIndexOf(", "));
        sql.append(" where id = '").append(index3Score.getId()).append("' ");
        jdbcTemplate.update(sql.toString());
    }

    public BigDecimal sumScoreByKesdi3IdAndKrtaId(String kesdi3Id, String krtaId) {
        String sql = "select sum(system_score) from kp_rating_task_index3_score where kesdi3_id = '" + kesdi3Id + "' and krta_id = '" + krtaId + "'";
        return jdbcTemplate.queryForObject(sql, BigDecimal.class);
    }

    public KpRatingTaskIndex3ScoreExpert getByExpertIdAndKesdi3IdAndKrtytId(String expertId, String kesdi3Id, String krtytId) {
        try {
            String sql = "select * from kp_rating_task_index3_score_expert where expert_id = '" + expertId + "' and kesdi3_id = '" + kesdi3Id + "' and krtyt_id = '" + krtytId + "'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(KpRatingTaskIndex3ScoreExpert.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }
}
